﻿// =================================================================== 
// 项目说明
//====================================================================
// liucx @Copy Right 2019
// 文件： VehicleLicenseRepository.cs
// 项目名称：斑马车务
// 创建时间：2019/9/17
// 负责人：liucx
// ===================================================================
using System;
using System.Text;
using System.Collections.Generic;
using YouWei.Infrastructure.Entity;
using YouWei.Infrastructure.Models;
using Dapper;
using Dapper.Extensions;

namespace YouWei.Infrastructure.Repository
{
    /// <summary>
    /// 仓储层
    /// </summary>	
    public class VehicleLicenseRepository : BaseRepository<VehicleLicense>
    {

        #region 构造

        public VehicleLicenseRepository() : base() { }

        public VehicleLicenseRepository(string connectionString) : base(connectionString) { }

        public VehicleLicenseRepository(DatabaseContext dbContext) : base(dbContext) { }

        #endregion

        /// <summary>
        /// 根据用户ID获取行驶证详情 真实情况可能1个用户有多个行驶证，这里暂时只展示一条
        /// </summary>
        /// <param name="mobile"></param>
        /// <returns></returns>
        public List<VehicleLicense> GetByUser(int UserID)
        {
            StringBuilder sb = new StringBuilder("SELECT * FROM VehicleLicense T WITH(NOLOCK) ");
            sb.Append(" WHERE T.UserID = @UserID");
            return Query(sb.ToString(), new { UserID = UserID });
        }

        /// <summary>
        /// 根据用户ID获取行驶证详情
        /// </summary>
        /// <param name="mobile"></param>
        /// <returns></returns>
        public VehicleLicense GetByUserPlateNum(int UserID, string PlateNo)
        {
            StringBuilder sb = new StringBuilder("SELECT * FROM VehicleLicense T WITH(NOLOCK) ");
            sb.Append(" WHERE T.UserID = @UserID AND T.PlateNo = @PlateNo");
            return FirstOrDefault(sb.ToString(), new { UserID = UserID, PlateNo = PlateNo });
        }

        /// <summary>
        /// 根据车型ID获取用户行驶证信息
        /// </summary>
        /// <param name="modelId"></param>
        /// <returns></returns>
        public List<VehicleUser> GetListByModelId(int modelId)
        {
            StringBuilder sb = new StringBuilder("SELECT T.UserID,U.OpenID,U.UnionID FROM VehicleLicense T WITH(NOLOCK) INNER JOIN UserInfo U WITH(NOLOCK)ON T.UserID = U.UserID");
            sb.Append(" WHERE IsActive=1 and T.ModelID = @ModelID");
            DynamicParameters dparams = new DynamicParameters();
            dparams.Add("@ModelID", modelId, System.Data.DbType.Int32);
            return dbContext.Connection.Query<VehicleUser>(sb.ToString(), dparams).AsList();
        }

        /// <summary>
        /// 查询行驶证列表
        /// 此方法仅用于管理员
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public Page<VehicleLicenseMode> Pages(int pageIndex, int pageSize, VehicleLicenseSearchModel model)
        {
            StringBuilder sb = new StringBuilder("SELECT  T1.*,b.UserName,b.Mobile FROM [VehicleLicense] T1 WITH(NOLOCK) ");
            //注：base_locationAll表里的locationId是主键，userinfo表里的location是地区，意义不一样。所以这里拿Hcity=locationid   liucx
            sb.Append(" Inner Join UserInfo b WITH(NOLOCK) ON T1.UserID= b.UserID");
            sb.Append(" WHERE T1.IsActive=1");
            var dparams = new DynamicParameters();
            if (model != null)
            {
                AddParams(model, sb, dparams);
            }
            sb.Append(" ORDER BY T1.VehicleLicenseID DESC");
            return dbContext.Connection.Page<VehicleLicenseMode>(pageIndex, pageSize, sb.ToString(), dparams);
        }


        /// <summary>
        /// 构建查询条件
        /// </summary>
        /// <param name="model"></param>
        /// <param name="sb"></param>
        /// <param name="dparams"></param>
        private void AddParams(VehicleLicenseSearchModel model, StringBuilder sb, DynamicParameters dparams)
        {
            if (!string.IsNullOrEmpty(model.EngineNo))
            {
                sb.Append(" AND charindex(@EngineNo,T1.EngineNo)>0");
                dparams.Add("EngineNo", model.EngineNo, System.Data.DbType.String);
            }
            if (!string.IsNullOrEmpty(model.CarName))
            {
                sb.Append(" AND charindex(@CarName,T1.CarName)>0");
                dparams.Add("CarName", model.CarName, System.Data.DbType.String);
            }
            if (!string.IsNullOrEmpty(model.PlateNo))
            {
                sb.Append(" AND charindex(@PlateNo,T1.PlateNo)>0");
                dparams.Add("PlateNo", model.PlateNo, System.Data.DbType.String);
            }
            if (!string.IsNullOrEmpty(model.VehicleType))
            {
                sb.Append(" AND T1.VehicleType=@VehicleType");
                dparams.Add("VehicleType", model.VehicleType, System.Data.DbType.String);
            }
             if (!string.IsNullOrEmpty(model.VIN))
            {
                sb.Append(" AND T1.VIN=@VIN");
                dparams.Add("VIN", model.VIN, System.Data.DbType.String);
            }
            if (!string.IsNullOrEmpty(model.Name))
            {
                sb.Append(" AND charindex(@UserName,b.UserName)>0");
                dparams.Add("UserName", model.Name, System.Data.DbType.String);
            }
        }

        /// <summary>
        /// 获取审核列表
        /// </summary>
        /// <returns></returns>
        public List<VehicleLicense> GetInspectList()
        {
            StringBuilder sb = new StringBuilder("SELECT TOP 10 * FROM VehicleLicense T WITH(NOLOCK) ");
            sb.Append(" WHERE IsActive=1 AND VehicleStatus<2 ORDER BY VehicleLicenseID ASC");
            return Query(sb.ToString());
        }
    }
}